excel code list A Guide to Excel Spreadsheets in Python With openpyxl – Real Python

A Guide to Excel Spreadsheets in Python With openpyxlby Pedro Pregueirointermediate data-viz

Table of Contents

Before You BeginPractical Use CasesLearning Some Basic Excel TerminologyGetting Started With openpyxlReading Excel Spreadsheets With openpyxlDataset for This TutorialA Simple Approach to Reading an Excel SpreadsheetImporting Data From a SpreadsheetAppending New DataWriting Excel Spreadsheets With openpyxlCreating a Simple SpreadsheetBasic Spreadsheet OperationsAdding FormulasAdding StylesConditional FormattingAdding ImagesAdding Pretty ChartsConvert Python Classes to Excel SpreadsheetBonus: Working With PandasConclusionRemove ads

Watch Now This tutorial has a related video course created by the Real Python team. Watch it together with the written tutorial to deepen your understanding: Editing Excel Spreadsheets in Python With openpyxl

Excel spreadsheets are one of those things you might have to deal with at some point. Either it’s because your boss loves them or because marketing needs them, you might have to learn how to work with spreadsheets, and that’s when knowing openpyxl comes in handy!

Spreadsheets are a very intuitive and user-friendly way to manipulate large datasets without any prior technical background. That’s why they’re still so commonly used today.

In this article, you’ll learn how to use openpyxl to:

Manipulate Excel spreadsheets with confidenceExtract information from spreadsheetsCreate simple or more complex spreadsheets, including adding styles, charts, and so on

This article is written for intermediate developers who have a pretty good knowledge of Python data structures, such as dicts and lists, but also feel comfortable around OOP and more intermediate level topics.

Download Dataset: Click here to download the dataset for the openpyxl exercise you’ll be following in this tutorial.

Before You Begin

If you ever get asked to extract some data from a database or log file into an Excel spreadsheet, or if you often have to convert an Excel spreadsheet into some more usable programmatic form, then this tutorial is perfect for you. Let’s jump into the openpyxl caravan!

Remove adsPractical Use Cases

First things first, when would you need to use a package like openpyxl in a real-world scenario? You’ll see a few examples below, but really, there are hundreds of possible scenarios where this knowledge could come in handy.

Importing New Products Into a Database

You are responsible for tech in an online store company, and your boss doesn’t want to pay for a cool and expensive CMS system.

Every time they want to add new products to the online store, they come to you with an Excel spreadsheet with a few hundred rows and, for each of them, you have the product name, description, price, and so forth.

Now, to import the data, you’ll have to iterate over each spreadsheet row and add each product to the online store.

Exporting Database Data Into a Spreadsheet

Say you have a Database table where you record all your users’ information, including name, phone number, email address, and so forth.

Now, the Marketing team wants to contact all users to give them some discounted offer or promotion. However, they don’t have access to the Database, or they don’t know how to use SQL to extract that information easily.

What can you do to help? Well, you can make a quick script using openpyxl that iterates over every single User record and puts all the essential information into an Excel spreadsheet.

That’s gonna earn you an extra slice of cake at your company’s next birthday party!

Appending Information to an Existing Spreadsheet

You may also have to open a spreadsheet, read the information in it and, according to some business logic, append more data to it.

For example, using the online store scenario again, say you get an Excel spreadsheet with a list of users and you need to append to each row the total amount they’ve spent in your store.

This data is in the Database and, in order to do this, you have to read the spreadsheet, iterate through each row, fetch the total amount spent from the Database and then write back to the spreadsheet.

Not a problem for openpyxl!

Learning Some Basic Excel Terminology

Here’s a quick list of basic terms you’ll see when you’re working with Excel spreadsheets:

TermExplanationSpreadsheet or WorkbookA Spreadsheet is the main file you are creating or working with.Worksheet or SheetA Sheet is used to split different kinds of content within the same spreadsheet. A Spreadsheet can have one or more Sheets.ColumnA Column is a vertical line, and it’s represented by an uppercase letter: A.RowA Row is a horizontal line, and it’s represented by a number: 1.CellA Cell is a combination of Column and Row, represented by both an uppercase letter and a number: A1.Getting Started With openpyxl

Now that you’re aware of the benefits of a tool like openpyxl, let’s get down to it and start by installing the package. For this tutorial, you should use Python 3.7 and openpyxl 2.6.2. To install the package, you can do the following:

Shell$ pip install openpyxl Copied!

After you install the package, you should be able to create a super simple spreadsheet with the following code:

Python from openpyxl import Workbookworkbook = Workbook()sheet = workbook.activesheet["A1"] = "hello"sheet["B1"] = "world!"workbook.save(filename="hello_world.xlsx") Copied!

The code above should create a file called hello_world.xlsx in the folder you are using to run the code. If you open that file with Excel you should see something like this:

Woohoo, your first spreadsheet created!

Remove adsReading Excel Spreadsheets With openpyxl

Let’s start with the most essential thing one can do with a spreadsheet: read it.

You’ll go from a straightforward approach to reading a spreadsheet to more complex examples where you read the data and convert it into more useful Python structures.

Dataset for This Tutorial

Before you dive deep into some code examples, you should download this sample dataset and store it somewhere as sample.xlsx:

Download Dataset: Click here to download the dataset for the openpyxl exercise you’ll be following in this tutorial.

This is one of the datasets you’ll be using throughout this tutorial, and it’s a spreadsheet with a sample of real data from Amazon’s online product reviews. This dataset is only a tiny fraction of what Amazon provides, but for testing purposes, it’s more than enough.

A Simple Approach to Reading an Excel Spreadsheet

Finally, let’s start reading some spreadsheets! To begin with, open our sample spreadsheet:

Python>>> from openpyxl import load_workbook>>> workbook = load_workbook(filename="sample.xlsx")>>> workbook.sheetnames['Sheet 1']>>> sheet = workbook.active>>> sheet>>> sheet.title'Sheet 1' Copied!

In the code above, you first open the spreadsheet sample.xlsx using load_workbook(), and then you can use workbook.sheetnames to see all the sheets you have available to work with. After that, workbook.active selects the first available sheet and, in this case, you can see that it selects Sheet 1 automatically. Using these methods is the default way of opening a spreadsheet, and you’ll see it many times during this tutorial.

Now, after opening a spreadsheet, you can easily retrieve data from it like this:

Python>>> sheet["A1"]>>> sheet["A1"].value'marketplace'>>> sheet["F10"].value"G-Shock Men's Grey Sport Watch" Copied!

To return the actual value of a cell, you need to do .value. Otherwise, you’ll get the main Cell object. You can also use the method .cell() to retrieve a cell using index notation. Remember to add .value to get the actual value and not a Cell object:

Python>>> sheet.cell(row=10, column=6)>>> sheet.cell(row=10, column=6).value"G-Shock Men's Grey Sport Watch" Copied!

You can see that the results returned are the same, no matter which way you decide to go with. However, in this tutorial, you’ll be mostly using the first approach: ["A1"].

Note: Even though in Python you’re used to a zero-indexed notation, with spreadsheets you’ll always use a one-indexed notation where the first row or column always has index 1.

The above shows you the quickest way to open a spreadsheet. However, you can pass additional parameters to change the way a spreadsheet is loaded.

Additional Reading Options

There are a few arguments you can pass to load_workbook() that change the way a spreadsheet is loaded. The most important ones are the following two Booleans:

read_only loads a spreadsheet in read-only mode allowing you to open very large Excel files.data_only ignores loading formulas and instead loads only the resulting values.Remove adsImporting Data From a Spreadsheet

Now that you’ve learned the basics about loading a spreadsheet, it’s about time you get to the fun part: the iteration and actual usage of the values within the spreadsheet.

This section is where you’ll learn all the different ways you can iterate through the data, but also how to convert that data into something usable and, more importantly, how to do it in a Pythonic way.

Iterating Through the Data

There are a few different ways you can iterate through the data depending on your needs.

You can slice the data with a combination of columns and rows:

Python>>> sheet["A1:C2"]((, , ), (, , )) Copied!

You can get ranges of rows or columns:

Python>>> # Get all cells from column A>>> sheet["A"](, , ... , )>>> # Get all cells for a range of columns>>> sheet["A:B"]((, , ... , ), (, , ... , ))>>> # Get all cells from row 5>>> sheet[5](, , ... , )>>> # Get all cells for a range of rows>>> sheet[5:6]((, , ... , ), (, , ... , )) Copied!

You’ll notice that all of the above examples return a tuple. If you want to refresh your memory on how to handle tuples in Python, check out the article on Lists and Tuples in Python.

There are also multiple ways of using normal Python generators to go through the data. The main methods you can use to achieve this are:

.iter_rows().iter_cols()

Both methods can receive the following arguments:

min_rowmax_rowmin_colmax_col

These arguments are used to set boundaries for the iteration:

Python>>> for row in sheet.iter_rows(min_row=1,...max_row=2,...min_col=1,...max_col=3):... print(row)(, , )(, , )>>> for column in sheet.iter_cols(min_row=1,...max_row=2,...min_col=1,...max_col=3):... print(column)(, )(, )(, ) Copied!

You’ll notice that in the first example, when iterating through the rows using .iter_rows(), you get one tuple element per row selected. While when using .iter_cols() and iterating through columns, you’ll get one tuple per column instead.

One additional argument you can pass to both methods is the Boolean values_only. When it’s set to True, the values of the cell are returned, instead of the Cell object:

Python>>> for value in sheet.iter_rows(min_row=1,... max_row=2,... min_col=1,... max_col=3,... values_only=True):... print(value)('marketplace', 'customer_id', 'review_id')('US', 3653882, 'R3O9SGZBVQBV76') Copied!

If you want to iterate through the whole dataset, then you can also use the attributes .rows or .columns directly, which are shortcuts to using .iter_rows() and .iter_cols() without any arguments:

Python>>> for row in sheet.rows:... print(row)(, , ..., , ) Copied!

These shortcuts are very useful when you’re iterating through the whole dataset.

Manipulate Data Using Python’s Default Data Structures

Now that you know the basics of iterating through the data in a workbook, let’s look at smart ways of converting that data into Python structures.

As you saw earlier, the result from all iterations comes in the form of tuples. However, since a tuple is nothing more than a list that’s immutable, you can easily access its data and transform it into other structures.

For example, say you want to extract product information from the sample.xlsx spreadsheet and into a dictionary where each key is a product ID.

A straightforward way to do this is to iterate over all the rows, pick the columns you know are related to product information, and then store that in a dictionary. Let’s code this out!

First of all, have a look at the headers and see what information you care most about:

Python>>> for value in sheet.iter_rows(min_row=1,... max_row=1,... values_only=True):... print(value)('marketplace', 'customer_id', 'review_id', 'product_id', ...) Copied!

This code returns a list of all the column names you have in the spreadsheet. To start, grab the columns with names:

product_idproduct_parentproduct_titleproduct_category

Lucky for you, the columns you need are all next to each other so you can use the min_column and max_column to easily get the data you want:

Python>>> for value in sheet.iter_rows(min_row=2,... min_col=4,... max_col=7,... values_only=True):... print(value)('B00FALQ1ZC', 937001370, 'Invicta Women\'s 15150 "Angel" 18k Yellow...)('B00D3RGO20', 484010722, "Kenneth Cole New York Women's KC4944...)... Copied!

Nice! Now that you know how to get all the important product information you need, let’s put that data into a dictionary:

Python import jsonfrom openpyxl import load_workbookworkbook = load_workbook(filename="sample.xlsx")sheet = workbook.activeproducts = {}# Using the values_only because you want to return the cells' valuesfor row in sheet.iter_rows(min_row=2,min_col=4,max_col=7,values_only=True):product_id = row[0]product = {"parent": row[1],"title": row[2],"category": row[3]}products[product_id] = product# Using json here to be able to format the output for displaying laterprint(json.dumps(products)) Copied!

The code above returns a JSON similar to this:

JSON { "B00FALQ1ZC": {"parent": 937001370,"title": "Invicta Women's 15150 ...","category": "Watches" }, "B00D3RGO20": {"parent": 484010722,"title": "Kenneth Cole New York ...","category": "Watches" }} Copied!

Here you can see that the output is trimmed to 2 products only, but if you run the script as it is, then you should get 98 products.

Convert Data Into Python Classes

To finalize the reading section of this tutorial, let’s dive into Python classes and see how you could improve on the example above and better structure the data.

For this, you’ll be using the new Python Data Classes that are available from Python 3.7. If you’re using an older version of Python, then you can use the default Classes instead.

So, first things first, let’s look at the data you have and decide what you want to store and how you want to store it.

As you saw right at the start, this data comes from Amazon, and it’s a list of product reviews. You can check the list of all the columns and their meaning on Amazon.

There are two significant elements you can extract from the data available:

ProductsReviews

A Product has:

IDTitleParentCategory

The Review has a few more fields:

IDCustomer IDStarsHeadlineBodyDate

You can ignore a few of the review fields to make things a bit simpler.

So, a straightforward implementation of these two classes could be written in a separate file classes.py:

Python import datetimefrom dataclasses import dataclass@dataclassclass Product:id: strparent: strtitle: strcategory: str@dataclassclass Review:id: strcustomer_id: strstars: intheadline: strbody: strdate: datetime.datetime Copied!

After defining your data classes, you need to convert the data from the spreadsheet into these new structures.

Before doing the conversion, it’s worth looking at our header again and creating a mapping between columns and the fields you need:

Python>>> for value in sheet.iter_rows(min_row=1,... max_row=1,... values_only=True):... print(value)('marketplace', 'customer_id', 'review_id', 'product_id', ...)>>> # Or an alternative>>> for cell in sheet[1]:... print(cell.value)marketplacecustomer_idreview_idproduct_idproduct_parent... Copied!

Let’s create a file mapping.py where you have a list of all the field names and their column location (zero-indexed) on the spreadsheet:

Python # Product fieldsPRODUCT_ID = 3PRODUCT_PARENT = 4PRODUCT_TITLE = 5PRODUCT_CATEGORY = 6# Review fieldsREVIEW_ID = 2REVIEW_CUSTOMER = 1REVIEW_STARS = 7REVIEW_HEADLINE = 12REVIEW_BODY = 13REVIEW_DATE = 14 Copied!

You don’t necessarily have to do the mapping above. It’s more for readability when parsing the row data, so you don’t end up with a lot of magic numbers lying around.

Finally, let’s look at the code needed to parse the spreadsheet data into a list of product and review objects:

Python from datetime import datetimefrom openpyxl import load_workbookfrom classes import Product, Reviewfrom mapping import PRODUCT_ID, PRODUCT_PARENT, PRODUCT_TITLE, \PRODUCT_CATEGORY, REVIEW_DATE, REVIEW_ID, REVIEW_CUSTOMER, \REVIEW_STARS, REVIEW_HEADLINE, REVIEW_BODY# Using the read_only method since you're not gonna be editing the spreadsheetworkbook = load_workbook(filename="sample.xlsx", read_only=True)sheet = workbook.activeproducts = []reviews = []# Using the values_only because you just want to return the cell valuefor row in sheet.iter_rows(min_row=2, values_only=True):product = Product(id=row[PRODUCT_ID], parent=row[PRODUCT_PARENT], title=row[PRODUCT_TITLE], category=row[PRODUCT_CATEGORY])products.append(product)# You need to parse the date from the spreadsheet into a datetime formatspread_date = row[REVIEW_DATE]parsed_date = datetime.strptime(spread_date, "%Y-%m-%d")review = Review(id=row[REVIEW_ID],customer_id=row[REVIEW_CUSTOMER],stars=row[REVIEW_STARS],headline=row[REVIEW_HEADLINE],body=row[REVIEW_BODY],date=parsed_date)reviews.append(review)print(products[0])print(reviews[0]) Copied!

After you run the code above, you should get some output like this:

Python Product(id='B00FALQ1ZC', parent=937001370, ...)Review(id='R3O9SGZBVQBV76', customer_id=3653882, ...) Copied!

That’s it! Now you should have the data in a very simple and digestible class format, and you can start thinking of storing this in a Database or any other type of data storage you like.

Using this kind of OOP strategy to parse spreadsheets makes handling the data much simpler later on.

Remove adsAppending New Data

Before you start creating very complex spreadsheets, have a quick look at an example of how to append data to an existing spreadsheet.

Go back to the first example spreadsheet you created (hello_world.xlsx) and try opening it and appending some data to it, like this:

Python from openpyxl import load_workbook# Start by opening the spreadsheet and selecting the main sheetworkbook = load_workbook(filename="hello_world.xlsx")sheet = workbook.active# Write what you want into a specific cellsheet["C1"] = "writing ;)"# Save the spreadsheetworkbook.save(filename="hello_world_append.xlsx") Copied!

Et voilà, if you open the new hello_world_append.xlsx spreadsheet, you’ll see the following change:

Notice the additional writing ;) on cell C1.

Writing Excel Spreadsheets With openpyxl

There are a lot of different things you can write to a spreadsheet, from simple text or number values to complex formulas, charts, or even images.

Let’s start creating some spreadsheets!

Creating a Simple Spreadsheet

Previously, you saw a very quick example of how to write “Hello world!” into a spreadsheet, so you can start with that:

Python1from openpyxl import Workbook 2 3filename = "hello_world.xlsx" 4 5workbook = Workbook() 6sheet = workbook.active 7 8sheet["A1"] = "hello" 9sheet["B1"] = "world!"1011workbook.save(filename=filename) Copied!

The highlighted lines in the code above are the most important ones for writing. In the code, you can see that:

Line 5 shows you how to create a new empty workbook.Lines 8 and 9 show you how to add data to specific cells.Line 11 shows you how to save the spreadsheet when you’re done.

Even though these lines above can be straightforward, it’s still good to know them well for when things get a bit more complicated.

Note: You’ll be using the hello_world.xlsx spreadsheet for some of the upcoming examples, so keep it handy.

One thing you can do to help with coming code examples is add the following method to your Python file or console:

Python>>> def print_rows():... for row in sheet.iter_rows(values_only=True):... print(row) Copied!

It makes it easier to print all of your spreadsheet values by just calling print_rows().

Remove adsBasic Spreadsheet Operations

Before you get into the more advanced topics, it’s good for you to know how to manage the most simple elements of a spreadsheet.

Adding and Updating Cell Values

You already learned how to add values to a spreadsheet like this:

Python>>> sheet["A1"] = "value" Copied!

There’s another way you can do this, by first selecting a cell and then changing its value:

Python>>> cell = sheet["A1"]>>> cell>>> cell.value'hello'>>> cell.value = "hey">>> cell.value'hey' Copied!

The new value is only stored into the spreadsheet once you call workbook.save().

The openpyxl creates a cell when adding a value, if that cell didn’t exist before:

Python>>> # Before, our spreadsheet has only 1 row>>> print_rows()('hello', 'world!')>>> # Try adding a value to row 10>>> sheet["B10"] = "test">>> print_rows()('hello', 'world!')(None, None)(None, None)(None, None)(None, None)(None, None)(None, None)(None, None)(None, None)(None, 'test') Copied!

As you can see, when trying to add a value to cell B10, you end up with a tuple with 10 rows, just so you can have that test value.

Managing Rows and Columns

One of the most common things you have to do when manipulating spreadsheets is adding or removing rows and columns. The openpyxl package allows you to do that in a very straightforward way by using the methods:

.insert_rows().delete_rows().insert_cols().delete_cols()

Every single one of those methods can receive two arguments:

idxamount

Using our basic hello_world.xlsx example again, let’s see how these methods work:

Python>>> print_rows()('hello', 'world!')>>> # Insert a column before the existing column 1 ("A")>>> sheet.insert_cols(idx=1)>>> print_rows()(None, 'hello', 'world!')>>> # Insert 5 columns between column 2 ("B") and 3 ("C")>>> sheet.insert_cols(idx=3, amount=5)>>> print_rows()(None, 'hello', None, None, None, None, None, 'world!')>>> # Delete the created columns>>> sheet.delete_cols(idx=3, amount=5)>>> sheet.delete_cols(idx=1)>>> print_rows()('hello', 'world!')>>> # Insert a new row in the beginning>>> sheet.insert_rows(idx=1)>>> print_rows()(None, None)('hello', 'world!')>>> # Insert 3 new rows in the beginning>>> sheet.insert_rows(idx=1, amount=3)>>> print_rows()(None, None)(None, None)(None, None)(None, None)('hello', 'world!')>>> # Delete the first 4 rows>>> sheet.delete_rows(idx=1, amount=4)>>> print_rows()('hello', 'world!') Copied!

The only thing you need to remember is that when inserting new data (rows or columns), the insertion happens before the idx parameter.

So, if you do insert_rows(1), it inserts a new row before the existing first row.

It’s the same for columns: when you call insert_cols(2), it inserts a new column right before the already existing second column (B).

However, when deleting rows or columns, .delete_... deletes data starting from the index passed as an argument.

For example, when doing delete_rows(2) it deletes row 2, and when doing delete_cols(3) it deletes the third column (C).

Managing Sheets

Sheet management is also one of those things you might need to know, even though it might be something that you don’t use that often.

If you look back at the code examples from this tutorial, you’ll notice the following recurring piece of code:

Python sheet = workbook.active Copied!

This is the way to select the default sheet from a spreadsheet. However, if you’re opening a spreadsheet with multiple sheets, then you can always select a specific one like this:

Python>>> # Let's say you have two sheets: "Products" and "Company Sales">>> workbook.sheetnames['Products', 'Company Sales']>>> # You can select a sheet using its title>>> products_sheet = workbook["Products"]>>> sales_sheet = workbook["Company Sales"] Copied!

You can also change a sheet title very easily:

Python>>> workbook.sheetnames['Products', 'Company Sales']>>> products_sheet = workbook["Products"]>>> products_sheet.title = "New Products">>> workbook.sheetnames['New Products', 'Company Sales'] Copied!

If you want to create or delete sheets, then you can also do that with .create_sheet() and .remove():

Python>>> workbook.sheetnames['Products', 'Company Sales']>>> operations_sheet = workbook.create_sheet("Operations")>>> workbook.sheetnames['Products', 'Company Sales', 'Operations']>>> # You can also define the position to create the sheet at>>> hr_sheet = workbook.create_sheet("HR", 0)>>> workbook.sheetnames['HR', 'Products', 'Company Sales', 'Operations']>>> # To remove them, just pass the sheet as an argument to the .remove()>>> workbook.remove(operations_sheet)>>> workbook.sheetnames['HR', 'Products', 'Company Sales']>>> workbook.remove(hr_sheet)>>> workbook.sheetnames['Products', 'Company Sales'] Copied!

One other thing you can do is make duplicates of a sheet using copy_worksheet():

Python>>> workbook.sheetnames['Products', 'Company Sales']>>> products_sheet = workbook["Products"]>>> workbook.copy_worksheet(products_sheet)>>> workbook.sheetnames['Products', 'Company Sales', 'Products Copy'] Copied!

If you open your spreadsheet after saving the above code, you’ll notice that the sheet Products Copy is a duplicate of the sheet Products.

Freezing Rows and Columns

Something that you might want to do when working with big spreadsheets is to freeze a few rows or columns, so they remain visible when you scroll right or down.

Freezing data allows you to keep an eye on important rows or columns, regardless of where you scroll in the spreadsheet.

Again, openpyxl also has a way to accomplish this by using the worksheet freeze_panes attribute. For this example, go back to our sample.xlsx spreadsheet and try doing the following:

Python>>> workbook = load_workbook(filename="sample.xlsx")>>> sheet = workbook.active>>> sheet.freeze_panes = "C2">>> workbook.save("sample_frozen.xlsx") Copied!

If you open the sample_frozen.xlsx spreadsheet in your favorite spreadsheet editor, you’ll notice that row 1 and columns A and B are frozen and are always visible no matter where you navigate within the spreadsheet.

This feature is handy, for example, to keep headers within sight, so you always know what each column represents.

Here’s how it looks in the editor:

Notice how you’re at the end of the spreadsheet, and yet, you can see both row 1 and columns A and B.

Adding Filters

You can use openpyxl to add filters and sorts to your spreadsheet. However, when you open the spreadsheet, the data won’t be rearranged according to these sorts and filters.

At first, this might seem like a pretty useless feature, but when you’re programmatically creating a spreadsheet that is going to be sent and used by somebody else, it’s still nice to at least create the filters and allow people to use it afterward.

The code below is an example of how you would add some filters to our existing sample.xlsx spreadsheet:

Python>>> # Check the used spreadsheet space using the attribute "dimensions">>> sheet.dimensions'A1:O100'>>> sheet.auto_filter.ref = "A1:O100">>> workbook.save(filename="sample_with_filters.xlsx") Copied!

You should now see the filters created when opening the spreadsheet in your editor:

You don’t have to use sheet.dimensions if you know precisely which part of the spreadsheet you want to apply filters to.

Remove adsAdding Formulas

Formulas (or formulae) are one of the most powerful features of spreadsheets.

They gives you the power to apply specific mathematical equations to a range of cells. Using formulas with openpyxl is as simple as editing the value of a cell.

You can see the list of formulas supported by openpyxl:

Python>>> from openpyxl.utils import FORMULAE>>> FORMULAEfrozenset({'ABS','ACCRINT','ACCRINTM','ACOS','ACOSH','AMORDEGRC','AMORLINC','AND',...'YEARFRAC','YIELD','YIELDDISC','YIELDMAT','ZTEST'}) Copied!

Let’s add some formulas to our sample.xlsx spreadsheet.

Starting with something easy, let’s check the average star rating for the 99 reviews within the spreadsheet:

Python>>> # Star rating is column "H">>> sheet["P2"] = "=AVERAGE(H2:H100)">>> workbook.save(filename="sample_formulas.xlsx") Copied!

If you open the spreadsheet now and go to cell P2, you should see that its value is: 4.18181818181818. Have a look in the editor:

You can use the same methodology to add any formulas to your spreadsheet. For example, let’s count the number of reviews that had helpful votes:

Python>>> # The helpful votes are counted on column "I">>> sheet["P3"] = '=COUNTIF(I2:I100, ">0")'>>> workbook.save(filename="sample_formulas.xlsx") Copied!

You should get the number 21 on your P3 spreadsheet cell like so:

You’ll have to make sure that the strings within a formula are always in double quotes, so you either have to use single quotes around the formula like in the example above or you’ll have to escape the double quotes inside the formula: "=COUNTIF(I2:I100, \">0\")".

There are a ton of other formulas you can add to your spreadsheet using the same procedure you tried above. Give it a go yourself!

Adding Styles

Even though styling a spreadsheet might not be something you would do every day, it’s still good to know how to do it.

Using openpyxl, you can apply multiple styling options to your spreadsheet, including fonts, borders, colors, and so on. Have a look at the openpyxl documentation to learn more.

You can also choose to either apply a style directly to a cell or create a template and reuse it to apply styles to multiple cells.

Let’s start by having a look at simple cell styling, using our sample.xlsx again as the base spreadsheet:

Python>>> # Import necessary style classes>>> from openpyxl.styles import Font, Color, Alignment, Border, Side>>> # Create a few styles>>> bold_font = Font(bold=True)>>> big_red_text = Font(color="00FF0000", size=20)>>> center_aligned_text = Alignment(horizontal="center")>>> double_border_side = Side(border_style="double")>>> square_border = Border(top=double_border_side,...right=double_border_side,...bottom=double_border_side,...left=double_border_side)>>> # Style some cells!>>> sheet["A2"].font = bold_font>>> sheet["A3"].font = big_red_text>>> sheet["A4"].alignment = center_aligned_text>>> sheet["A5"].border = square_border>>> workbook.save(filename="sample_styles.xlsx") Copied!

If you open your spreadsheet now, you should see quite a few different styles on the first 5 cells of column A:

There you go. You got:

A2 with the text in boldA3 with the text in red and bigger font sizeA4 with the text centeredA5 with a square border around the text

Note: For the colors, you can also use HEX codes instead by doing Font(color="C70E0F").

You can also combine styles by simply adding them to the cell at the same time:

Python>>> # Reusing the same styles from the example above>>> sheet["A6"].alignment = center_aligned_text>>> sheet["A6"].font = big_red_text>>> sheet["A6"].border = square_border>>> workbook.save(filename="sample_styles.xlsx") Copied!

Have a look at cell A6 here:

When you want to apply multiple styles to one or several cells, you can use a NamedStyle class instead, which is like a style template that you can use over and over again. Have a look at the example below:

Python>>> from openpyxl.styles import NamedStyle>>> # Let's create a style template for the header row>>> header = NamedStyle(name="header")>>> header.font = Font(bold=True)>>> header.border = Border(bottom=Side(border_style="thin"))>>> header.alignment = Alignment(horizontal="center", vertical="center")>>> # Now let's apply this to all first row (header) cells>>> header_row = sheet[1]>>> for cell in header_row:... cell.style = header>>> workbook.save(filename="sample_styles.xlsx") Copied!

If you open the spreadsheet now, you should see that its first row is bold, the text is aligned to the center, and there’s a small bottom border! Have a look below:

As you saw above, there are many options when it comes to styling, and it depends on the use case, so feel free to check openpyxl documentation and see what other things you can do.

Remove adsConditional Formatting

This feature is one of my personal favorites when it comes to adding styles to a spreadsheet.

It’s a much more powerful approach to styling because it dynamically applies styles according to how the data in the spreadsheet changes.

In a nutshell, conditional formatting allows you to specify a list of styles to apply to a cell (or cell range) according to specific conditions.

For example, a widespread use case is to have a balance sheet where all the negative totals are in red, and the positive ones are in green. This formatting makes it much more efficient to spot good vs bad periods.

Without further ado, let’s pick our favorite spreadsheet—sample.xlsx—and add some conditional formatting.

You can start by adding a simple one that adds a red background to all reviews with less than 3 stars:

Python>>> from openpyxl.styles import PatternFill>>> from openpyxl.styles.differential import DifferentialStyle>>> from openpyxl.formatting.rule import Rule>>> red_background = PatternFill(fgColor="00FF0000")>>> diff_style = DifferentialStyle(fill=red_background)>>> rule = Rule(type="expression", dxf=diff_style)>>> rule.formula = ["$H1